﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

using SIPO.Library;
using SIPO.Library.DomainObject.Administration.Catalog;
using SIPO.Library.DomainObject.Administration.UserManagement;
using SIPO.Library.DomainObject.Transaction;
using SIPO.Library.Enum;
using SIPO.Library.Interfaces;

namespace SIPO.Library.DataAccess.Transaction
{
    public partial class recInvoiceRepo : IRepository<recInvoice, string>
    {
        public int GetMaxID()
        {
            int result = 1;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                                begin
                                    declare @max int = 1;

                                    if Exists(SELECT MAX(CONVERT(INT,SUBSTRING(InvoiceNo,11,5))) InvoiceNo FROM transactions.recInvoice where SUBSTRING(InvoiceNo,5,2) = MONTH(GETDATE()))
	                                    set @max = (SELECT MAX(CONVERT(INT,SUBSTRING(InvoiceNo,11,5)))+1 InvoiceNo FROM transactions.recInvoice where SUBSTRING(InvoiceNo,5,2) = MONTH(GETDATE()));
                                    else
	                                    set @max = 1;

                                    select @max;	
                                end ";
                    cmd.CommandType = CommandType.Text;

                    try
                    {
                        conn.Open();
                        var objId = cmd.ExecuteScalar();
                        result = objId == DBNull.Value ? 1 : int.Parse(objId.ToString());
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return result;
        }

        public int GetMaxID(string sectorCode, string subSectorCode)
        {
            int result = 1;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                                begin
                                    declare @max int = 1;

                                    if Exists(SELECT MAX(CONVERT(INT,SUBSTRING(InvoiceNo,11,5))) InvoiceNo FROM transactions.recInvoice where SUBSTRING(InvoiceNo,5,2) = MONTH(GETDATE()) AND SUBSTRING(InvoiceNo,1,1)=@SectorCode AND SUBSTRING(InvoiceNo,3,1)=@SubSectorCode )
	                                    set @max = (SELECT MAX(CONVERT(INT,SUBSTRING(InvoiceNo,11,5)))+1 InvoiceNo FROM transactions.recInvoice where SUBSTRING(InvoiceNo,5,2) = MONTH(GETDATE()) AND SUBSTRING(InvoiceNo,1,1)=@SectorCode AND SUBSTRING(InvoiceNo,3,1)=@SubSectorCode);
                                    else
	                                    set @max = 1;

                                    select @max;	
                                end ";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@SectorCode", SqlDbType.VarChar).Value = sectorCode;
                    cmd.Parameters.Add("@SubSectorCode", SqlDbType.VarChar).Value = subSectorCode;
                    try
                    {
                        conn.Open();
                        var objId = cmd.ExecuteScalar();
                        result = objId == DBNull.Value ? 1 : int.Parse(objId.ToString());
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return result;
        }

        private bool GetMonthBillingNo()
        {
            bool result = false;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT TOP 1 SUBSTRING(InvoiceNo,3,2) Bulan  FROM transactions.recInvoice  where substring(InvoiceNo,1,1)='R'  ORDER BY InvoiceNo DESC";
                    cmd.CommandType = CommandType.Text;

                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {


                                if (dr["Bulan"] != DBNull.Value)
                                {
                                    if (dr["Bulan"].ToString().Trim() == DateTime.Now.ToString("MM"))
                                        result = true;
                                }

                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }

            return result;
        }

        public object SelectScalarReport(SQL.Function.Aggregate function, string column, string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                        case SQL.Function.Aggregate.Max:
                            sbQuery.AppendFormat("SELECT MAX({0}) FROM transactions.recInvoice", column);
                            sbQuery.AppendFormat("  inner join master.recBankAccount on transactions.recInvoice.BankID = master.recBankAccount.BankID");
                            sbQuery.AppendFormat("  inner join master.recDebtorSector on transactions.recInvoice.DebtorSectorID = master.recDebtorSector.DebtorSectorID");
                            sbQuery.AppendFormat("  inner join master.recSector on master.recDebtorSector.SectorID = master.recSector.SectorID");
                            sbQuery.AppendFormat("  inner join master.recSubSector on master.recDebtorSector.SubSectorID = master.recSubSector.SubSectorID");
                            sbQuery.AppendFormat("  inner join master.recSubSubSector on master.recDebtorSector.SubSubSectorID = master.recSubSubSector.SubSubSectorID");
                            sbQuery.AppendFormat("  inner join master.recDebtor on master.recDebtorSector.DebtorID = master.recDebtor.DebtorID  {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Min:
                            sbQuery.AppendFormat("SELECT MIN({0}) FROM transactions.recInvoice", column);
                            sbQuery.AppendFormat("  inner join master.recBankAccount on transactions.recInvoice.BankID = master.recBankAccount.BankID");
                            sbQuery.AppendFormat("  inner join master.recDebtorSector on transactions.recInvoice.DebtorSectorID = master.recDebtorSector.DebtorSectorID");
                            sbQuery.AppendFormat("  inner join master.recSector on master.recDebtorSector.SectorID = master.recSector.SectorID");
                            sbQuery.AppendFormat("  inner join master.recSubSector on master.recDebtorSector.SubSectorID = master.recSubSector.SubSectorID");
                            sbQuery.AppendFormat("  inner join master.recSubSubSector on master.recDebtorSector.SubSubSectorID = master.recSubSubSector.SubSubSectorID");
                            sbQuery.AppendFormat("  inner join master.recDebtor on master.recDebtorSector.DebtorID = master.recDebtor.DebtorID  {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Distinct:
                            sbQuery.AppendFormat("SELECT DISTINCT({0}) FROM transactions.recInvoice", column);
                            sbQuery.AppendFormat("  inner join master.recBankAccount on transactions.recInvoice.BankID = master.recBankAccount.BankID");
                            sbQuery.AppendFormat("  inner join master.recDebtorSector on transactions.recInvoice.DebtorSectorID = master.recDebtorSector.DebtorSectorID");
                            sbQuery.AppendFormat("  inner join master.recSector on master.recDebtorSector.SectorID = master.recSector.SectorID");
                            sbQuery.AppendFormat("  inner join master.recSubSector on master.recDebtorSector.SubSectorID = master.recSubSector.SubSectorID");
                            sbQuery.AppendFormat("  inner join master.recSubSubSector on master.recDebtorSector.SubSubSectorID = master.recSubSubSector.SubSubSectorID");
                            sbQuery.AppendFormat("  inner join master.recDebtor on master.recDebtorSector.DebtorID = master.recDebtor.DebtorID  {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat("SELECT COUNT({0}) FROM transactions.recInvoice", column);
                            sbQuery.AppendFormat("  inner join master.recBankAccount on transactions.recInvoice.BankID = master.recBankAccount.BankID");
                            sbQuery.AppendFormat("  inner join master.recDebtorSector on transactions.recInvoice.DebtorSectorID = master.recDebtorSector.DebtorSectorID");
                            sbQuery.AppendFormat("  inner join master.recSector on master.recDebtorSector.SectorID = master.recSector.SectorID");
                            sbQuery.AppendFormat("  inner join master.recSubSector on master.recDebtorSector.SubSectorID = master.recSubSector.SubSectorID");
                            sbQuery.AppendFormat("  inner join master.recSubSubSector on master.recDebtorSector.SubSubSectorID = master.recSubSubSector.SubSubSectorID");
                            sbQuery.AppendFormat("  inner join master.recDebtor on master.recDebtorSector.DebtorID = master.recDebtor.DebtorID  {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Sum:
                            sbQuery.AppendFormat("SELECT SUM({0}) FROM transactions.recInvoice", column);
                            sbQuery.AppendFormat("  inner join master.recBankAccount on transactions.recInvoice.BankID = master.recBankAccount.BankID");
                            sbQuery.AppendFormat("  inner join master.recDebtorSector on transactions.recInvoice.DebtorSectorID = master.recDebtorSector.DebtorSectorID");
                            sbQuery.AppendFormat("  inner join master.recSector on master.recDebtorSector.SectorID = master.recSector.SectorID");
                            sbQuery.AppendFormat("  inner join master.recSubSector on master.recDebtorSector.SubSectorID = master.recSubSector.SubSectorID");
                            sbQuery.AppendFormat("  inner join master.recSubSubSector on master.recDebtorSector.SubSubSectorID = master.recSubSubSector.SubSubSectorID");
                            sbQuery.AppendFormat("  inner join master.recDebtor on master.recDebtorSector.DebtorID = master.recDebtor.DebtorID  {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Avg:
                            sbQuery.AppendFormat("SELECT AVG({0}) FROM transactions.recInvoice", column);
                            sbQuery.AppendFormat("  inner join master.recBankAccount on transactions.recInvoice.BankID = master.recBankAccount.BankID");
                            sbQuery.AppendFormat("  inner join master.recDebtorSector on transactions.recInvoice.DebtorSectorID = master.recDebtorSector.DebtorSectorID");
                            sbQuery.AppendFormat("  inner join master.recSector on master.recDebtorSector.SectorID = master.recSector.SectorID");
                            sbQuery.AppendFormat("  inner join master.recSubSector on master.recDebtorSector.SubSectorID = master.recSubSector.SubSectorID");
                            sbQuery.AppendFormat("  inner join master.recSubSubSector on master.recDebtorSector.SubSubSectorID = master.recSubSubSector.SubSubSectorID");
                            sbQuery.AppendFormat("  inner join master.recDebtor on master.recDebtorSector.DebtorID = master.recDebtor.DebtorID  {0}", parameters);
                            break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }
        public object SelectScalarHistoryReport(SQL.Function.Aggregate function, string column, string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    string paramPay = string.Empty;
                    //column = "recbilling.billingno";
                    // string columnpay = "recpayment.paymentno";
                    paramPay = parameters.Replace("Billing", "Payment");
                    // paramPay = parameters.Replace("recBilling.debtorsectorid", "recPayment.DebtorSectorid");
                    // paramPay = parameters.Replace("recBilling.billingno", "recPayment.paymentno");
                    switch (function)
                    {
                        case SQL.Function.Aggregate.Max:
                            sbQuery.AppendFormat("    SELECT ");
                            sbQuery.AppendFormat(@"     max(NoTrans)
                                                from
                                                (
                                                select
                                                transactions.recBilling.BillingNo as 'NoTrans'");
                            sbQuery.AppendFormat("    FROM ");
                            sbQuery.AppendFormat(@"transactions.recBilling
                                            INNER JOIN master.recTransactionType ON transactions.recBilling.TransTypeID = master.recTransactionType.TransTypeID");
                            sbQuery.AppendFormat(" {0} ", parameters);
                            sbQuery.AppendFormat("    UNION ");
                            sbQuery.AppendFormat("    SELECT ");
                            sbQuery.AppendFormat(@" transactions.recPayment.PaymentNo");
                            sbQuery.AppendFormat("  FROM ");
                            sbQuery.AppendFormat(@"transactions.recPayment
                                                ");
                            sbQuery.AppendFormat("  {0} ", paramPay);
                            sbQuery.AppendFormat(" ) x");
                            break;
                        case SQL.Function.Aggregate.Min:
                            sbQuery.AppendFormat("    SELECT ");
                            sbQuery.AppendFormat(@"     min(NoTrans)
                                                from
                                                (
                                                select
                                                transactions.recBilling.BillingNo as 'NoTrans'");
                            sbQuery.AppendFormat("    FROM ");
                            sbQuery.AppendFormat(@"transactions.recBilling
                                            INNER JOIN master.recTransactionType ON transactions.recBilling.TransTypeID = master.recTransactionType.TransTypeID");
                            sbQuery.AppendFormat(" {0} ", parameters);
                            sbQuery.AppendFormat("    UNION ");
                            sbQuery.AppendFormat("    SELECT ");
                            sbQuery.AppendFormat(@" transactions.recPayment.PaymentNo");
                            sbQuery.AppendFormat("  FROM ");
                            sbQuery.AppendFormat(@"transactions.recPayment
                                                ");
                            sbQuery.AppendFormat("  {0} ", paramPay);
                            sbQuery.AppendFormat(" ) x");
                            break;
                        case SQL.Function.Aggregate.Distinct:
                            sbQuery.AppendFormat("    SELECT ");
                            sbQuery.AppendFormat(@"     distinct(NoTrans)
                                                from
                                                (
                                                select
                                                transactions.recBilling.BillingNo as 'NoTrans'");
                            sbQuery.AppendFormat("    FROM ");
                            sbQuery.AppendFormat(@"transactions.recBilling
                                            INNER JOIN master.recTransactionType ON transactions.recBilling.TransTypeID = master.recTransactionType.TransTypeID");
                            sbQuery.AppendFormat(" {0} ", parameters);
                            sbQuery.AppendFormat("    UNION ");
                            sbQuery.AppendFormat("    SELECT ");
                            sbQuery.AppendFormat(@" transactions.recPayment.PaymentNo");
                            sbQuery.AppendFormat("  FROM ");
                            sbQuery.AppendFormat(@"transactions.recPayment
                                                ");
                            sbQuery.AppendFormat("  {0} ", paramPay);
                            sbQuery.AppendFormat(" ) x");
                            break;
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat("    SELECT ");
                            sbQuery.AppendFormat(@"     count(NoTrans)
                                                from
                                                (
                                                select
                                                transactions.recBilling.BillingNo as 'NoTrans'");
                            sbQuery.AppendFormat("    FROM ");
                            sbQuery.AppendFormat(@"transactions.recBilling
                                            INNER JOIN master.recTransactionType ON transactions.recBilling.TransTypeID = master.recTransactionType.TransTypeID");
                            sbQuery.AppendFormat(" {0} ", parameters);
                            sbQuery.AppendFormat("    UNION ");
                            sbQuery.AppendFormat("    SELECT ");
                            sbQuery.AppendFormat(@" transactions.recPayment.PaymentNo");
                            sbQuery.AppendFormat("  FROM ");
                            sbQuery.AppendFormat(@"transactions.recPayment
                                                ");
                            sbQuery.AppendFormat("  {0} ", paramPay);
                            sbQuery.AppendFormat(" ) x");
                            break;
                        case SQL.Function.Aggregate.Sum:
                            sbQuery.AppendFormat("    SELECT ");
                            sbQuery.AppendFormat(@"     sum(NoTrans)
                                                from
                                                (
                                                select
                                                transactions.recBilling.BillingNo as 'NoTrans'");
                            sbQuery.AppendFormat("    FROM ");
                            sbQuery.AppendFormat(@"transactions.recBilling
                                            INNER JOIN master.recTransactionType ON transactions.recBilling.TransTypeID = master.recTransactionType.TransTypeID");
                            sbQuery.AppendFormat(" {0} ", parameters);
                            sbQuery.AppendFormat("    UNION ");
                            sbQuery.AppendFormat("    SELECT ");
                            sbQuery.AppendFormat(@" transactions.recPayment.PaymentNo");
                            sbQuery.AppendFormat("  FROM ");
                            sbQuery.AppendFormat(@"transactions.recPayment
                                                ");
                            sbQuery.AppendFormat("  {0} ", paramPay);
                            sbQuery.AppendFormat(" ) x");
                            break;
                        case SQL.Function.Aggregate.Avg:
                            sbQuery.AppendFormat("    SELECT ");
                            sbQuery.AppendFormat(@"     avg(NoTrans)
                                                from
                                                (
                                                select
                                                transactions.recBilling.BillingNo as 'NoTrans'");
                            sbQuery.AppendFormat("    FROM ");
                            sbQuery.AppendFormat(@"transactions.recBilling
                                            INNER JOIN master.recTransactionType ON transactions.recBilling.TransTypeID = master.recTransactionType.TransTypeID");
                            sbQuery.AppendFormat(" {0} ", parameters);
                            sbQuery.AppendFormat("    UNION ");
                            sbQuery.AppendFormat("    SELECT ");
                            sbQuery.AppendFormat(@" transactions.recPayment.PaymentNo");
                            sbQuery.AppendFormat("  FROM ");
                            sbQuery.AppendFormat(@"transactions.recPayment
                                                ");
                            sbQuery.AppendFormat("  {0} ", paramPay);
                            sbQuery.AppendFormat(" ) x");
                            break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }
        public object SelectScalarHistoryReport(SQL.Function.Aggregate function, string column, string parameters, string parametersInvoiceNo)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    string paramPay = string.Empty;
                    paramPay = parameters.Replace("Billing", "Payment");
                    string paramPay2 = string.Empty;
                    paramPay2 = parametersInvoiceNo.Replace("Billing", "Payment");
                    switch (function)
                    {
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat("    SELECT ");
                            sbQuery.AppendFormat(@"     count(NoTrans)
                                                from
                                                (
                                                select
                                                transactions.recBilling.BillingNo as 'NoTrans'");
                            sbQuery.AppendFormat("    FROM ");
                            sbQuery.AppendFormat(@"transactions.recBilling
                                            INNER JOIN master.recTransactionType ON transactions.recBilling.TransTypeID = master.recTransactionType.TransTypeID");
                            sbQuery.AppendFormat(" {0} ", parameters);
                            sbQuery.AppendFormat("    UNION ");
                            sbQuery.AppendFormat("    SELECT ");
                            sbQuery.AppendFormat(@" transactions.recPayment.PaymentNo");
                            sbQuery.AppendFormat("  FROM ");
                            sbQuery.AppendFormat(@"transactions.recPayment
                                                ");
                            sbQuery.AppendFormat("  {0} ", paramPay);
                            sbQuery.AppendFormat("    UNION ");
                            sbQuery.AppendFormat("    SELECT ");
                            sbQuery.AppendFormat(@"transactions.recBilling.BillingNo as 'NoTrans'
                                                ");
                            sbQuery.AppendFormat("    FROM ");
                            sbQuery.AppendFormat(@"transactions.recBilling
                                            INNER JOIN master.recTransactionType ON transactions.recBilling.TransTypeID = master.recTransactionType.TransTypeID");
                            sbQuery.AppendFormat(" {0} ", parametersInvoiceNo);
                            sbQuery.AppendFormat("    UNION ");
                            sbQuery.AppendFormat("    SELECT ");
                            sbQuery.AppendFormat(@" transactions.recPayment.PaymentNo");
                            sbQuery.AppendFormat("  FROM ");
                            sbQuery.AppendFormat(@"transactions.recPayment
                                                ");
                            sbQuery.AppendFormat("  {0} ", paramPay2);
                            sbQuery.AppendFormat(" ) x");
                            break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }

        public object SelectScalar(SQL.Function.Aggregate function, string column, string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                        case SQL.Function.Aggregate.Max:
                            sbQuery.AppendFormat("SELECT MAX({0}) FROM transactions.recInvoice", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Min:
                            sbQuery.AppendFormat("SELECT MIN({0}) FROM transactions.recInvoice", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Distinct:
                            sbQuery.AppendFormat("SELECT DISTINCT({0}) FROM transactions.recInvoice", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat(@"SELECT COUNT({0}) FROM transactions.recInvoice 
                                                    LEFT JOIN master.recDebtorSector ON recInvoice.DebtorSectorID = recDebtorSector.DebtorSectorID 
                                                    LEFT JOIN master.recSubSubSector ON recDebtorSector.SubSubSectorID = recSubSubSector.SubSubSectorID {1} ", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Sum:
                            sbQuery.AppendFormat("SELECT SUM({0}) FROM transactions.recInvoice", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Avg:
                            sbQuery.AppendFormat("SELECT AVG({0}) FROM transactions.recInvoice", column, parameters);
                            break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }

        public object SelectScalarUpdatePembayaran(SQL.Function.Aggregate function, string column,string queryInvoice,string queryAksi,string queryRegistrasi, string parameters,int bankID)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {

                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat(@"
                                        SELECT COUNT({0})
                                        FROM
                                        (",column);

                    sbQuery.AppendFormat(queryRegistrasi);
                    sbQuery.AppendFormat(" UNION ");
                    if (queryAksi.Trim().Length > 1)
                    {
                        sbQuery.AppendFormat(queryAksi);
                        sbQuery.AppendFormat(" UNION ");
                    }

                    sbQuery.AppendFormat(queryInvoice);
                    sbQuery.AppendFormat(" ) recInvoice {0}",parameters);

                            break;

                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.Parameters.Add("@BankID", SqlDbType.Int).Value = bankID;
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }

        public List<recInvoice> GetListReport(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            List<recInvoice> tt = new List<recInvoice>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat(@"     transactions.recInvoice.InvoiceNo, 
                                                master.recDebtor.DebtorName,
                                                transactions.recInvoice.InvoiceDate, 
                                                transactions.recInvoice.BankID, 
                                                master.recBankAccount.BankCode, 
                                                master.recBankAccount.BankName, 
                                                master.recBankAccount.AccountCode,
                                                transactions.recInvoice.InvoiceValue,
                                                master.recDebtorSector.DebtorSectorID,
                                                master.recSector.SectorName,
                                                master.recSubSector.SubSectorName,
                                                master.recSubSubSector.SubSubSectorName");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"     transactions.recInvoice 
                                                inner join master.recBankAccount on transactions.recInvoice.BankID = master.recBankAccount.BankID
                                                inner join master.recDebtorSector on transactions.recInvoice.DebtorSectorID = master.recDebtorSector.DebtorSectorID
                                                inner join master.recSector on master.recDebtorSector.SectorID = master.recSector.SectorID
                                                inner join master.recSubSector on master.recDebtorSector.SubSectorID = master.recSubSector.SubSectorID
                                                inner join master.recSubSubSector on master.recDebtorSector.SubSubSectorID = master.recSubSubSector.SubSubSectorID
                                                inner join master.recDebtor on master.recDebtorSector.DebtorID = master.recDebtor.DebtorID");
                    sbQuery.AppendFormat("      {0} ", parameters);
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recInvoice t = new recInvoice(true);
                                t.InvoiceNo = dr[recInvoice.Fields.InvoiceNo] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.InvoiceNo].ToString();
                                t.InvoiceDate = dr[recInvoice.Fields.InvoiceDate] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[recInvoice.Fields.InvoiceDate].ToString());
                                t.InvoiceValue = dr[recInvoice.Fields.InvoiceValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recInvoice.Fields.InvoiceValue].ToString());
                                t.BankID = dr[recInvoice.Fields.BankID] == DBNull.Value ? (short)0 : short.Parse(dr[recInvoice.Fields.BankID].ToString());
                                t.recDebtor.DebtorName = dr[recDebtor.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorName].ToString();
                                t.recBankAccount.BankCode = dr[recBankAccount.Fields.BankCode] == DBNull.Value ? string.Empty : dr[recBankAccount.Fields.BankCode].ToString();
                                t.recBankAccount.BankName = dr[recBankAccount.Fields.BankName] == DBNull.Value ? string.Empty : dr[recBankAccount.Fields.BankName].ToString();
                                t.recBankAccount.AccountCode = dr[recBankAccount.Fields.AccountCode] == DBNull.Value ? string.Empty : dr[recBankAccount.Fields.AccountCode].ToString();
                                t.recDebtorSector.DebtorSectorID = dr[recDebtorSector.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtorSector.Fields.DebtorSectorID].ToString());
                                t.recSector.SectorName = dr[recSector.Fields.SectorName] == DBNull.Value ? string.Empty : dr[recSector.Fields.SectorName].ToString();
                                t.recSubSector.SubSectorName = dr[recSubSector.Fields.SubSectorName] == DBNull.Value ? string.Empty : dr[recSubSector.Fields.SubSectorName].ToString();
                                t.recSubSubSector.SubSubSectorName = dr[recSubSubSector.Fields.SubSubSectorName] == DBNull.Value ? string.Empty : dr[recSubSubSector.Fields.SubSubSectorName].ToString();

                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public List<recInvoice> GetList(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            List<recInvoice> tt = new List<recInvoice>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat(@"      recInvoice.InvoiceNo
                                                , recInvoice.InvoiceDate
                                                , recInvoice.InvoiceValue
                                                , recInvoice.BankID
                                                , recInvoice.BeSpelled
                                                , recInvoice.PaymentNo
                                                , recInvoice.PaymentDate
                                                , recInvoice.PaymentValue
                                                , recInvoice.IsHapusBuku
                                                , recInvoice.IsHapusTagih
                                                , recInvoice.DebtorSectorID
                                                , recInvoice.CreatedDate
                                                , recInvoice.CreatedBy
                                                , recInvoice.UpdatedDate
                                                , recInvoice.UpdatedBy
                                                ,recDebtor.DebtorName
                                                ,recSubSubSector.SubSubSectorName
                                                ");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"     transactions.recInvoice 
                                                LEFT JOIN master.recDebtorSector ON recInvoice.DebtorSectorID = recDebtorSector.DebtorSectorID
                                                LEFT JOIN master.recDebtor ON recDebtorSector.DebtorID = recDebtor.DebtorID
                                                LEFT JOIN master.recSubSubSector ON recDebtorSector.SubSubSectorID = recSubSubSector.SubSubSectorID
                                                
                                                ");
                    sbQuery.AppendFormat("      {0} ", parameters);
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recInvoice t = new recInvoice(true);
                                t.InvoiceNo = dr[recInvoice.Fields.InvoiceNo] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.InvoiceNo].ToString();
                                t.InvoiceDate = dr[recInvoice.Fields.InvoiceDate] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[recInvoice.Fields.InvoiceDate].ToString());
                                t.InvoiceValue = dr[recInvoice.Fields.InvoiceValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recInvoice.Fields.InvoiceValue].ToString());
                                t.BankID = dr[recInvoice.Fields.BankID] == DBNull.Value ? (short)0 : short.Parse(dr[recInvoice.Fields.BankID].ToString());
                                t.BeSpelled = dr[recInvoice.Fields.BeSpelled] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.BeSpelled].ToString();
                                t.PaymentNo = dr[recInvoice.Fields.PaymentNo] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.PaymentNo].ToString();
                                t.PaymentDate = dr[recInvoice.Fields.PaymentDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recInvoice.Fields.PaymentDate].ToString());
                                t.PaymentValue = dr[recInvoice.Fields.PaymentValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recInvoice.Fields.PaymentValue].ToString());
                                t.IsHapusBuku = dr[recInvoice.Fields.IsHapusBuku] == DBNull.Value ? (bool?)null : bool.Parse(dr[recInvoice.Fields.IsHapusBuku].ToString());
                                t.IsHapusTagih = dr[recInvoice.Fields.IsHapusTagih] == DBNull.Value ? (bool?)null : bool.Parse(dr[recInvoice.Fields.IsHapusTagih].ToString());
                                t.DebtorSectorID = dr[recInvoice.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recInvoice.Fields.DebtorSectorID].ToString());
                                t.CreatedDate = dr[recInvoice.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recInvoice.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recInvoice.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recInvoice.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recInvoice.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recInvoice.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.UpdatedBy].ToString();


                                t.recDebtorSector = new recDebtorSector(true);
                                t.recDebtorSector.recDebtor.DebtorName = dr[recDebtor.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorName].ToString();
                                t.recDebtorSector.recSubSubSector.SubSubSectorName = dr[recSubSubSector.Fields.SubSubSectorName] == DBNull.Value ? string.Empty : dr[recSubSubSector.Fields.SubSubSectorName].ToString();

                                //t.recInvoiceDetail = new recInvoiceDetail(true);
                                //t.recInvoiceDetail.Value = dr[recInvoiceDetail.Fields.Value] == DBNull.Value ? 0 : decimal.Parse(dr[recInvoiceDetail.Fields.Value].ToString());
                                //t.recInvoiceDetail.InvoiceDetailID = dr[recInvoiceDetail.Fields.InvoiceDetailID] == DBNull.Value ? 0 : int.Parse(dr[recInvoiceDetail.Fields.InvoiceDetailID].ToString());

                                //t.recInvoiceDetail.recTransactionType.TransTypeName = dr[recTransactionType.Fields.TransTypeName] == DBNull.Value ? string.Empty : dr[recTransactionType.Fields.TransTypeName].ToString();
                                //t.recInvoiceDetail.DueDate = dr[recInvoiceDetail.Fields.DueDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recInvoiceDetail.Fields.DueDate].ToString());

                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public List<recInvoice> GetListUpdatePembayaran(int pageSize, int currentPage, string sortName, string sortOrder,string queryInvoice,string queryAksi,string queryRegistrasi, string parameters,int bankID)
        {
            List<recInvoice> tt = new List<recInvoice>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);


                    sbQuery.AppendFormat(@"
                                         DebtorSectorID
                                        ,InvoiceNo
                                        ,InvoiceDate
                                        ,InvoiceValue
                                        ,BankID
                                        ,BeSpelled
                                        ,PaymentNo
                                        ,PaymentDate
                                        ,InvoiceValue PaymentValue
                                        ,IsHapusBuku
                                        ,IsHapusTagih
                                        ,CreatedDate
                                        ,CreatedBy
                                        ,UpdatedDate
                                        ,UpdatedBy
                                        ,IsCancel
                                        ,Balance
                                        ,DebtorName
                                        FROM
                                        (");

                    sbQuery.AppendFormat(queryRegistrasi);
                    sbQuery.AppendFormat(" UNION ");
                    if (queryAksi.Trim().Length > 1)
                    {
                        sbQuery.AppendFormat(queryAksi);
                        sbQuery.AppendFormat(" UNION ");
                    }

                    sbQuery.AppendFormat(queryInvoice);
                    sbQuery.AppendFormat(" ) recInvoice {0}",parameters);

                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.Parameters.Add("@BankID", SqlDbType.Int).Value =bankID;
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recInvoice t = new recInvoice(true);
                                t.InvoiceNo = dr[recInvoice.Fields.InvoiceNo] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.InvoiceNo].ToString();
                                t.InvoiceDate = dr[recInvoice.Fields.InvoiceDate] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[recInvoice.Fields.InvoiceDate].ToString());
                                t.InvoiceValue = dr[recInvoice.Fields.InvoiceValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recInvoice.Fields.InvoiceValue].ToString());
                                t.BankID = dr[recInvoice.Fields.BankID] == DBNull.Value ? (short)0 : short.Parse(dr[recInvoice.Fields.BankID].ToString());
                                t.BeSpelled = dr[recInvoice.Fields.BeSpelled] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.BeSpelled].ToString();
                                t.PaymentNo = dr[recInvoice.Fields.PaymentNo] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.PaymentNo].ToString();
                                t.PaymentDate = dr[recInvoice.Fields.PaymentDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recInvoice.Fields.PaymentDate].ToString());
                                t.PaymentValue = dr[recInvoice.Fields.PaymentValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recInvoice.Fields.PaymentValue].ToString());
                                t.IsHapusBuku = dr[recInvoice.Fields.IsHapusBuku] == DBNull.Value ? (bool?)null : bool.Parse(dr[recInvoice.Fields.IsHapusBuku].ToString());
                                t.IsHapusTagih = dr[recInvoice.Fields.IsHapusTagih] == DBNull.Value ? (bool?)null : bool.Parse(dr[recInvoice.Fields.IsHapusTagih].ToString());
                                t.DebtorSectorID = dr[recInvoice.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recInvoice.Fields.DebtorSectorID].ToString());
                                t.CreatedDate = dr[recInvoice.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recInvoice.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recInvoice.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recInvoice.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recInvoice.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recInvoice.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.UpdatedBy].ToString();


                                t.recDebtorSector = new recDebtorSector(true);
                                t.recDebtorSector.recDebtor.DebtorName = dr[recDebtor.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorName].ToString();
                                //t.recDebtorSector.recSubSubSector.SubSubSectorName = dr[recSubSubSector.Fields.SubSubSectorName] == DBNull.Value ? string.Empty : dr[recSubSubSector.Fields.SubSubSectorName].ToString();

                                //t.recInvoiceDetail = new recInvoiceDetail(true);
                                //t.recInvoiceDetail.Value = dr[recInvoiceDetail.Fields.Value] == DBNull.Value ? 0 : decimal.Parse(dr[recInvoiceDetail.Fields.Value].ToString());
                                //t.recInvoiceDetail.InvoiceDetailID = dr[recInvoiceDetail.Fields.InvoiceDetailID] == DBNull.Value ? 0 : int.Parse(dr[recInvoiceDetail.Fields.InvoiceDetailID].ToString());

                                //t.recInvoiceDetail.recTransactionType.TransTypeName = dr[recTransactionType.Fields.TransTypeName] == DBNull.Value ? string.Empty : dr[recTransactionType.Fields.TransTypeName].ToString();
                                //t.recInvoiceDetail.DueDate = dr[recInvoiceDetail.Fields.DueDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recInvoiceDetail.Fields.DueDate].ToString());

                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public bool Save(recInvoice domain, List<recInvoiceDetail> invoiceDetail)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    //====INSERT MASTER======================================================
                    string insertMaster = @"INSERT INTO transactions.recInvoice(
                                        InvoiceNo
                                        , InvoiceDate
                                        , InvoiceValue
                                        , BankID
                                        , BeSpelled
                                        , PaymentNo
                                        , PaymentDate
                                        , PaymentValue
                                        , IsHapusBuku
                                        , IsHapusTagih
                                        , DebtorSectorID
                                        , CreatedDate
                                        , CreatedBy
                                        , UpdatedDate
                                        , UpdatedBy
                                        , Balance) 
                                        VALUES(
                                        @InvoiceNo
                                        , @InvoiceDate
                                        , @InvoiceValue
                                        , @BankID
                                        , @BeSpelled
                                        , @PaymentNo
                                        , @PaymentDate
                                        , @PaymentValue
                                        , @IsHapusBuku
                                        , @IsHapusTagih
                                        , @DebtorSectorID
                                        , @CreatedDate
                                        , @CreatedBy
                                        , @UpdatedDate
                                        , @UpdatedBy
                                        , @Balance)";

                    SqlCommand cmd = new SqlCommand(insertMaster, conn);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;
                    cmd.Parameters.Add("@InvoiceDate", SqlDbType.DateTime).Value = domain.InvoiceDate;
                    cmd.Parameters.Add("@InvoiceValue", SqlDbType.Decimal).Value = (object)domain.InvoiceValue ?? DBNull.Value;
                    cmd.Parameters.Add("@BankID", SqlDbType.SmallInt).Value = domain.BankID;
                    cmd.Parameters.Add("@BeSpelled", SqlDbType.VarChar).Value = (object)domain.BeSpelled ?? DBNull.Value;
                    cmd.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = (object)domain.PaymentNo ?? DBNull.Value;
                    cmd.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = (object)domain.PaymentDate ?? DBNull.Value;
                    cmd.Parameters.Add("@PaymentValue", SqlDbType.Decimal).Value = (object)domain.PaymentValue ?? DBNull.Value;
                    cmd.Parameters.Add("@IsHapusBuku", SqlDbType.Bit).Value = (object)domain.IsHapusBuku ?? DBNull.Value;
                    cmd.Parameters.Add("@IsHapusTagih", SqlDbType.Bit).Value = (object)domain.IsHapusTagih ?? DBNull.Value;
                    cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = domain.DebtorSectorID;
                    cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)domain.CreatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)domain.CreatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@Balance", SqlDbType.Decimal).Value = (object)domain.Balance ?? DBNull.Value;

                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();

                    //================================================================

                    //===========INSERT DETAIL

                    invoiceDetail.ForEach(delegate(recInvoiceDetail domainDetail)
                    {

                        string insertDetail = @"INSERT INTO transactions.recInvoiceDetail(
                                                InvoiceNo
                                                , Value
                                                , TransTypeID
                                                , TariffID
                                                , BillingNo
                                                , DueDate
                                                , ForPayment
                                                , CreatedDate
                                                , CreatedBy
                                                , UpdatedDate
                                                , UpdatedBy)
                                                VALUES(@InvoiceNo
                                                , @Value
                                                , @TransTypeID
                                                , @TariffID
                                                , @BillingNo
                                                , @DueDate
                                                , @ForPayment
                                                , @CreatedDate
                                                , @CreatedBy
                                                , @UpdatedDate
                                                , @UpdatedBy)";
                        SqlCommand cmdDtl = new SqlCommand(insertDetail, conn);
                        //cmdDtl.Parameters.Add("@InvoiceDetailID", SqlDbType.SmallInt).Value = domainDetail.InvoiceDetailID;
                        cmdDtl.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;
                        cmdDtl.Parameters.Add("@Value", SqlDbType.Decimal).Value = domainDetail.Value;
                        cmdDtl.Parameters.Add("@TransTypeID", SqlDbType.SmallInt).Value = domainDetail.TransTypeID;
                        cmdDtl.Parameters.Add("@TariffID", SqlDbType.Int).Value = (object)domainDetail.TariffID ?? DBNull.Value;
                        cmdDtl.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = (object)domainDetail.BillingNo ?? DBNull.Value;
                        cmdDtl.Parameters.Add("@DueDate", SqlDbType.DateTime).Value = (object)domainDetail.DueDate ?? DBNull.Value;
                        cmdDtl.Parameters.Add("@ForPayment", SqlDbType.VarChar).Value = (object)domainDetail.ForPayment ?? DBNull.Value;
                        cmdDtl.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)domainDetail.CreatedDate ?? DBNull.Value;
                        cmdDtl.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)domainDetail.CreatedBy ?? DBNull.Value;
                        cmdDtl.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domainDetail.UpdatedDate ?? DBNull.Value;
                        cmdDtl.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domainDetail.UpdatedBy ?? DBNull.Value;

                        cmdDtl.Transaction = txn;
                        cmdDtl.ExecuteNonQuery();
                        cmdDtl.Dispose();

                        //================================================================


                        //Get InvoiceDtlID

                        string strInvoiceDetailID = @"SELECT InvoiceDetailID  FROM transactions.recInvoiceDetail WHERE CreatedDate=@CreatedDate AND CreatedBy=@CreatedBy ";
                        SqlCommand cmdInvoiceDetailID = new SqlCommand(strInvoiceDetailID, conn);
                        cmdInvoiceDetailID.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)domainDetail.CreatedDate ?? DBNull.Value;
                        cmdInvoiceDetailID.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)domainDetail.CreatedBy ?? DBNull.Value;

                        cmdInvoiceDetailID.Transaction = txn;
                        int InvoiceDetailID = Convert.ToInt16(cmdInvoiceDetailID.ExecuteScalar());
                        cmdInvoiceDetailID.Dispose();


                        //=====================



                        //Update Invoice No recBilling

                        string updateBilling = "UPDATE transactions.recBilling SET InvoiceNo=@InvoiceNo,InvoiceDetailID=@InvoiceDetailID WHERE BillingNo=@BillingNo";
                        SqlCommand cmdBilling = new SqlCommand(updateBilling, conn);
                        cmdBilling.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;
                        cmdBilling.Parameters.Add("@InvoiceDetailID", SqlDbType.SmallInt).Value = InvoiceDetailID;
                        cmdBilling.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = domainDetail.BillingNo;


                        cmdBilling.Transaction = txn;
                        cmdBilling.ExecuteNonQuery();
                        cmdBilling.Dispose();


                        ///============================================


                        //Update Invoice No  dan Invoice Date recSanksi

                        string updateSanksi = "UPDATE transactions.recSanction SET InvoiceNo=@InvoiceNo,InvoiceDate=@InvoiceDate WHERE BillingNo=@BillingNo";
                        SqlCommand cmdSanksi = new SqlCommand(updateSanksi, conn);
                        cmdSanksi.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = domain.InvoiceNo;
                        cmdSanksi.Parameters.Add("@InvoiceDate", SqlDbType.DateTime).Value = DateTime.Now;
                        cmdSanksi.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = domainDetail.BillingNo;


                        cmdSanksi.Transaction = txn;
                        cmdSanksi.ExecuteNonQuery();
                        cmdSanksi.Dispose();


                        ///============================================

                    });

                    result = true;
                    txn.Commit();

                    //=======================================================


                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }

            }
            return result;
        }

        public bool Delete(recInvoice invoice, recInvoiceDetail invoicedetail, recBilling billing)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    //====DELETE recInvoiceDetail ======================================================

                    string deleteInvoiceDtl = @"DELETE FROM transactions.recInvoiceDetail WHERE InvoiceDetailID = @InvoiceDetailID ";

                    SqlCommand cmd = new SqlCommand(deleteInvoiceDtl, conn);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@InvoiceDetailID", SqlDbType.Int).Value = invoicedetail.InvoiceDetailID;


                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();

                    //=======end

                    //===========UPDATE recInvoice

                    string updateInvoice = @"UPDATE transactions.recInvoice SET InvoiceValue=@InvoiceValue,UpdatedDate=@UpdatedDate,UpdatedBy=@UpdatedBy WHERE InvoiceNo=@InvoiceNo ";

                    SqlCommand cmdUpdate = new SqlCommand(updateInvoice, conn);
                    cmdUpdate.CommandType = CommandType.Text;
                    cmdUpdate.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = invoice.InvoiceNo;
                    cmdUpdate.Parameters.Add("@InvoiceValue", SqlDbType.Money).Value = invoice.InvoiceValue;
                    cmdUpdate.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = invoice.UpdatedDate;
                    cmdUpdate.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = invoice.UpdatedBy;


                    cmdUpdate.Transaction = txn;
                    cmdUpdate.ExecuteNonQuery();
                    cmdUpdate.Dispose();

                    //========== end

                    //===========UPDATE recBilling

                    string updateBilling = @"UPDATE transactions.recBilling SET InvoiceNo=NULL,InvoiceDetailID=NULL,UpdatedDate=@UpdatedDate,UpdatedBy=@UpdatedBy WHERE BillingNo=@BillingNo ";

                    SqlCommand cmdUpdateBilling = new SqlCommand(updateBilling, conn);
                    cmdUpdateBilling.CommandType = CommandType.Text;
                    //cmdUpdateBilling.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = billing.InvoiceNo;
                    //cmdUpdateBilling.Parameters.Add("@InvoiceDetailID", SqlDbType.Int).Value = billing.InvoiceDetailID;
                    cmdUpdateBilling.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = billing.UpdatedDate;
                    cmdUpdateBilling.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = billing.UpdatedBy;
                    cmdUpdateBilling.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = billing.BillingNo;

                    cmdUpdateBilling.Transaction = txn;
                    cmdUpdateBilling.ExecuteNonQuery();
                    cmdUpdateBilling.Dispose();

                    //========== end


                    result = true;
                    txn.Commit();

                    //=======================================================


                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }

            }
            return result;
        }

        public bool Delete(recInvoice invoice, List<recInvoiceDetail> invoicedetail)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    //====DELETE recInvoiceDetail ======================================================

                    string deleteInvoiceDtl = @"DELETE FROM transactions.recInvoiceDetail WHERE InvoiceNo = @InvoiceNo ";

                    SqlCommand cmd = new SqlCommand(deleteInvoiceDtl, conn);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = invoice.InvoiceNo;


                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();

                    //=======end

                    //====DELETE recInvoice ======================================================

                    string deleteInvoice = @"DELETE FROM transactions.recInvoice WHERE InvoiceNo = @InvoiceNo ";

                    SqlCommand cmdInvoice = new SqlCommand(deleteInvoice, conn);
                    cmdInvoice.CommandType = CommandType.Text;
                    cmdInvoice.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = invoice.InvoiceNo;


                    cmdInvoice.Transaction = txn;
                    cmdInvoice.ExecuteNonQuery();
                    cmdInvoice.Dispose();

                    //=======end


                    //==Update recBilling NoInvoice=Null dan InvoiceDetailID=NULL

                    invoicedetail.ForEach(delegate(recInvoiceDetail domainDetail)
                   {

                       string updateBilling = @"UPDATE  transactions.recBilling SET InvoiceNo=NULL,InvoiceDetailID=NULL WHERE BillingNo = @BillingNo ";

                       SqlCommand cmdBilling = new SqlCommand(updateBilling, conn);
                       cmdBilling.CommandType = CommandType.Text;
                       cmdBilling.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = domainDetail.BillingNo;


                       cmdBilling.Transaction = txn;
                       cmdBilling.ExecuteNonQuery();
                       cmdBilling.Dispose();
                   });

                    //=== end


                    result = true;
                    txn.Commit();


                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }

            }
            return result;
        }

        public recInvoice GetBeSpelledByInvoiceValue(decimal invoiceValue)
        {
            recInvoice t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT TOP 1 dbo.FN_Terbilang(@InvoiceValue)+' Rupiah' BeSpelled from transactions.recInvoice";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@InvoiceValue", SqlDbType.Decimal).Value = invoiceValue;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = new recInvoice();
                                t.BeSpelled = dr[recInvoice.Fields.BeSpelled] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.BeSpelled].ToString();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }

        public DataTable GetDatatableRptInvoice(string InvoiceNo)
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendLine("SELECT ROW_NUMBER () OVER (ORDER BY recInvoice.InvoiceNo ) AS RowNumber, recInvoice.InvoiceNo AS NomorInvoice");
                    sbQuery.AppendLine(",recDebtor.DebtorName AS Debitur");
                    sbQuery.AppendLine(",recSector.SectorName +'-'+recSubSector.SubSectorName +'-' + recSubSubSector.SubSubSectorName AS JenisUsaha");
                    sbQuery.AppendLine(",recInvoice.InvoiceValue AS JumlahTagihan");
                    sbQuery.AppendLine(",recTransactionType.TransTypeName AS Tagihan");
                    sbQuery.AppendLine(",recInvoiceDetail.ForPayment AS Keterangan");
                    sbQuery.AppendLine(",CONVERT(varchar(11),recInvoiceDetail.DueDate,103)  AS JatuhTempo");
                    sbQuery.AppendLine(",recInvoiceDetail.Value AS Jumlah");
                    sbQuery.AppendLine(",recBankAccount.BankName AS Bank");
                    sbQuery.AppendLine(",recBankAccount.AccountCode AS NoRekening");
                    sbQuery.AppendLine(",recBankAccount.AccountName AS AtasNama");
                    sbQuery.AppendLine(",recInvoice.BeSpelled AS Terbilang");
                    sbQuery.AppendLine(",dbo.FN_CekDueDate(CONVERT(varchar(10), DATEADD(DAY,2, transactions.recInvoice.InvoiceDate), 112)) InvoiceDate");
                    sbQuery.AppendLine("FROM");
                    sbQuery.AppendLine("transactions.recInvoice");
                    sbQuery.AppendLine("INNER JOIN transactions.recInvoiceDetail ON recInvoice.InvoiceNo = recInvoiceDetail.InvoiceNo");
                    sbQuery.AppendLine("INNER JOIN master.recDebtorSector ON recInvoice.DebtorSectorID  = recDebtorSector.DebtorSectorID ");
                    sbQuery.AppendLine("INNER JOIN master.recDebtor ON recDebtor.DebtorID = recDebtorSector.DebtorID");
                    sbQuery.AppendLine("INNER JOIN master.recTransactionType ON recInvoiceDetail.TransTypeID = recTransactionType.TransTypeID");
                    sbQuery.AppendLine("INNER JOIN master.recBankAccount ON recInvoice.BankID = recBankAccount.BankID");
                    sbQuery.AppendLine("INNER JOIN master.recSubSubSector ON recDebtorSector.SubSubSectorID = recSubSubSector.SubSubSectorID");
                    sbQuery.AppendLine("INNER JOIN master.recSubSector ON recDebtorSector.SubSectorID = recSubSector.SubSectorID");
                    sbQuery.AppendLine("INNER JOIN master.recSector ON recDebtorSector.SectorID = recSector.SectorID");
                    sbQuery.AppendLine("WHERE recInvoice.InvoiceNo = @InvoiceNo");

                    cmd.CommandText = sbQuery.ToString();
                    cmd.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = InvoiceNo;
                    //cmd.BindByName = true;


                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;

                    try
                    {
                        conn.Open();
                        da.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }

                }
            }

            return dt;

        }

        public DataTable GetListReport(string parameters)
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat(@"     transactions.recInvoice.InvoiceNo, 
                                                master.recDebtor.DebtorName,
                                                transactions.recInvoice.InvoiceDate, 
                                                transactions.recInvoice.BankID, 
                                                master.recBankAccount.BankCode, 
                                                master.recBankAccount.BankName, 
                                                master.recBankAccount.AccountCode,
                                                transactions.recInvoice.InvoiceValue,
                                                master.recDebtorSector.DebtorSectorID,
                                                master.recSector.SectorName,
                                                master.recSubSector.SubSectorName,
                                                master.recSubSubSector.SubSubSectorName");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"     transactions.recInvoice 
                                                inner join master.recBankAccount on transactions.recInvoice.BankID = master.recBankAccount.BankID
                                                inner join master.recDebtorSector on transactions.recInvoice.DebtorSectorID = master.recDebtorSector.DebtorSectorID
                                                inner join master.recSector on master.recDebtorSector.SectorID = master.recSector.SectorID
                                                inner join master.recSubSector on master.recDebtorSector.SubSectorID = master.recSubSector.SubSectorID
                                                inner join master.recSubSubSector on master.recDebtorSector.SubSubSectorID = master.recSubSubSector.SubSubSectorID
                                                inner join master.recDebtor on master.recDebtorSector.DebtorID = master.recDebtor.DebtorID");
                    sbQuery.AppendFormat("      {0} ", parameters);

                    cmd.CommandText = sbQuery.ToString();


                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;

                    try
                    {
                        conn.Open();
                        da.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return dt;
        }


        public List<recInvoice> GetListByBankID(int bankID,string queryRegistrasi,string queryInvoice,string queryAksi)
        {

            List<recInvoice> tt = new List<recInvoice>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    //cmd.CommandText = "SELECT DISTINCT InvoiceNo, InvoiceDate, InvoiceValue, BankID, BeSpelled, PaymentNo, PaymentDate, PaymentValue, IsHapusBuku, IsHapusTagih, DebtorSectorID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, IsCancel, Balance FROM transactions.recInvoice WHERE BankID=@BankID AND ISNULL(InvoiceValue,0) - ISNULL(PaymentValue,0)>0";
                    StringBuilder sql = new StringBuilder();
//                    sql.AppendFormat(
//                                       @"select A.DebtorSectorID
//                                        ,a.InvoiceNo
//                                        ,a.InvoiceDate
//                                        ,a.InvoiceValue
//                                        ,a.BankID
//                                        ,a.BeSpelled
//                                        ,a.PaymentNo
//                                        ,a.PaymentDate
//                                        ,a.PaymentValue
//                                        ,a.IsHapusBuku
//                                        ,a.IsHapusTagih
//                                        ,a.CreatedDate
//                                        ,a.CreatedBy
//                                        ,a.UpdatedDate
//                                        ,a.UpdatedBy
//                                        ,a.IsCancel
//                                        ,a.Balance
//                                        from transactions.recInvoice a 
//                                        inner join transactions.recbilling b on a.InvoiceNo = b.InvoiceNo
//                                        inner join transactions.recSectorRegistration c ON a.InvoiceNo = c.InvoiceNo 
//                                        where a.debtorsectorid = 0 and b.transtypeid = 1
//                                        and a.paymentno is null AND a.BankID = @BankID {0} ", subsubsectorIDsRegistrasi);
//                    sql.AppendFormat(@"
//                                                    UNION
//                                                    select A.DebtorSectorID
//                                                    ,A.InvoiceNo
//                                                    ,A.InvoiceDate
//                                                    ,A.InvoiceValue
//                                                    ,A.BankID
//                                                    ,A.BeSpelled
//                                                    ,A.PaymentNo
//                                                    ,A.PaymentDate
//                                                    ,A.PaymentValue
//                                                    ,A.IsHapusBuku
//                                                    ,A.IsHapusTagih
//                                                    ,A.CreatedDate
//                                                    ,A.CreatedBy
//                                                    ,A.UpdatedDate
//                                                    ,A.UpdatedBy
//                                                    ,A.IsCancel
//                                                    ,A.Balance 
//                                                    from transactions.recInvoice a 
//                                                    inner join transactions.recbilling b
//                                                    on a.InvoiceNo = b.InvoiceNo
//                                                    where a.debtorsectorid = 0 and b.transtypeid = 4
//                                                    and a.paymentno is null AND a.BankID = @BankID ");
//                    sql.AppendFormat(@"
//                                                    UNION 
//                                                    SELECT 
//                                                    recInvoice.DebtorSectorID
//                                                    ,InvoiceNo
//                                                    ,InvoiceDate
//                                                    ,InvoiceValue
//                                                    ,BankID
//                                                    ,BeSpelled
//                                                    ,PaymentNo
//                                                    ,PaymentDate
//                                                    ,PaymentValue
//                                                    ,IsHapusBuku
//                                                    ,IsHapusTagih
//                                                    ,recInvoice.CreatedDate
//                                                    ,recInvoice.CreatedBy
//                                                    ,recInvoice.UpdatedDate
//                                                    ,recInvoice.UpdatedBy
//                                                    ,IsCancel
//                                                    ,Balance
//                                                    FROM transactions.recInvoice INNER JOIN master.recDebtorSector on recInvoice.DebtorSectorID = recDebtorSector.DebtorSectorID
//                                                    WHERE recInvoice.DebtorSectorID<>0 AND recInvoice.PaymentNo IS NULL AND recInvoice.BankID = @BankID {0}", subsubsectorIDsInvoice);

                    sql.AppendFormat(@"SELECT
                                        DebtorSectorID
                                        ,InvoiceNo
                                        ,InvoiceDate
                                        ,InvoiceValue
                                        ,BankID
                                        ,BeSpelled
                                        ,PaymentNo
                                        ,PaymentDate
                                        ,PaymentValue
                                        ,IsHapusBuku
                                        ,IsHapusTagih
                                        ,CreatedDate
                                        ,CreatedBy
                                        ,UpdatedDate
                                        ,UpdatedBy
                                        ,IsCancel
                                        ,Balance
                                        FROM
                                        (");

                    sql.AppendFormat(queryRegistrasi);
                    sql.AppendFormat(" UNION ");
                    if (queryAksi.Trim().Length > 1)
                    {
                        sql.AppendFormat(queryAksi);
                        sql.AppendFormat(" UNION ");
                    }

                    sql.AppendFormat(queryInvoice);
                    sql.AppendFormat(" ) recInvoice");

                    cmd.Parameters.Add("@BankID", SqlDbType.Int).Value = bankID;
                    cmd.CommandText = sql.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recInvoice t = new recInvoice();
                                t.InvoiceNo = dr[recInvoice.Fields.InvoiceNo] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.InvoiceNo].ToString();
                                t.InvoiceDate = dr[recInvoice.Fields.InvoiceDate] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[recInvoice.Fields.InvoiceDate].ToString());
                                t.InvoiceValue = dr[recInvoice.Fields.InvoiceValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recInvoice.Fields.InvoiceValue].ToString());
                                t.BankID = dr[recInvoice.Fields.BankID] == DBNull.Value ? (short)0 : short.Parse(dr[recInvoice.Fields.BankID].ToString());
                                t.BeSpelled = dr[recInvoice.Fields.BeSpelled] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.BeSpelled].ToString();
                                t.PaymentNo = dr[recInvoice.Fields.PaymentNo] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.PaymentNo].ToString();
                                t.PaymentDate = dr[recInvoice.Fields.PaymentDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recInvoice.Fields.PaymentDate].ToString());
                                t.PaymentValue = dr[recInvoice.Fields.PaymentValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recInvoice.Fields.PaymentValue].ToString());
                                t.IsHapusBuku = dr[recInvoice.Fields.IsHapusBuku] == DBNull.Value ? (bool?)null : bool.Parse(dr[recInvoice.Fields.IsHapusBuku].ToString());
                                t.IsHapusTagih = dr[recInvoice.Fields.IsHapusTagih] == DBNull.Value ? (bool?)null : bool.Parse(dr[recInvoice.Fields.IsHapusTagih].ToString());
                                t.DebtorSectorID = dr[recInvoice.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recInvoice.Fields.DebtorSectorID].ToString());
                                t.CreatedDate = dr[recInvoice.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recInvoice.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recInvoice.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recInvoice.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recInvoice.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recInvoice.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.UpdatedBy].ToString();
                                t.IsCancel = dr[recInvoice.Fields.IsCancel] == DBNull.Value ? (bool?)null : bool.Parse(dr[recInvoice.Fields.IsCancel].ToString());
                                t.Balance = dr[recInvoice.Fields.Balance] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recInvoice.Fields.Balance].ToString());
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public List<recInvoice> GetListByInvoiceNo(string InvoiceNo)
        {
            List<recInvoice> tt = new List<recInvoice>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT DISTINCT InvoiceNo, InvoiceDate,InvoiceValue, BankID, BeSpelled, PaymentNo, PaymentDate, PaymentValue, IsHapusBuku, IsHapusTagih, DebtorSectorID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, IsCancel, Balance FROM transactions.recInvoice WHERE InvoiceNo=@InvoiceNo";
                    cmd.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = InvoiceNo;
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recInvoice t = new recInvoice();
                                t.InvoiceNo = dr[recInvoice.Fields.InvoiceNo] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.InvoiceNo].ToString();
                                t.InvoiceDate = dr[recInvoice.Fields.InvoiceDate] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[recInvoice.Fields.InvoiceDate].ToString());
                                t.InvoiceValue = dr[recInvoice.Fields.InvoiceValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recInvoice.Fields.InvoiceValue].ToString());
                                t.BankID = dr[recInvoice.Fields.BankID] == DBNull.Value ? (short)0 : short.Parse(dr[recInvoice.Fields.BankID].ToString());
                                t.BeSpelled = dr[recInvoice.Fields.BeSpelled] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.BeSpelled].ToString();
                                t.PaymentNo = dr[recInvoice.Fields.PaymentNo] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.PaymentNo].ToString();
                                t.PaymentDate = dr[recInvoice.Fields.PaymentDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recInvoice.Fields.PaymentDate].ToString());
                                t.PaymentValue = dr[recInvoice.Fields.PaymentValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recInvoice.Fields.PaymentValue].ToString());
                                t.IsHapusBuku = dr[recInvoice.Fields.IsHapusBuku] == DBNull.Value ? (bool?)null : bool.Parse(dr[recInvoice.Fields.IsHapusBuku].ToString());
                                t.IsHapusTagih = dr[recInvoice.Fields.IsHapusTagih] == DBNull.Value ? (bool?)null : bool.Parse(dr[recInvoice.Fields.IsHapusTagih].ToString());
                                t.DebtorSectorID = dr[recInvoice.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recInvoice.Fields.DebtorSectorID].ToString());
                                t.CreatedDate = dr[recInvoice.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recInvoice.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recInvoice.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recInvoice.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recInvoice.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recInvoice.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.UpdatedBy].ToString();
                                t.IsCancel = dr[recInvoice.Fields.IsCancel] == DBNull.Value ? (bool?)null : bool.Parse(dr[recInvoice.Fields.IsCancel].ToString());
                                t.Balance = dr[recInvoice.Fields.Balance] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recInvoice.Fields.Balance].ToString());
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }
        public List<recInvoice> GetListHistoryReport(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            List<recInvoice> tt = new List<recInvoice>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);
                    string paramPay = string.Empty;
                    paramPay = parameters.Replace("Billing", "Payment");
                    //paramPay = parameters.Replace("recBilling.debtorsectorid", "recPayment.DebtorSectorid");
                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat(@"     Date,NoTrans,JenTrans,Keterangan,Debet,Kredit
                                                from
                                                (
                                                select
                                                transactions.recBilling.Billingdate as 'Date', 
                                                 transactions.recBilling.BillingNo as 'NoTrans',
                                                 master.recTransactionType.TransTypeName as 'JenTrans',                                                 
                                                 transactions.recBilling.ForPayment as 'Keterangan',
                                                 case master.recTransactionType.TransTypeName
                                                 when 'Credit Note' then NULL
                                                 else transactions.recBilling.value end as 'Debet',
                                                 case master.recTransactionType.TransTypeName
                                                 when 'Credit Note' then transactions.recBilling.value 
                                                 else NULL end as 'Kredit'");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"transactions.recBilling
                                            INNER JOIN master.recTransactionType ON transactions.recBilling.TransTypeID = master.recTransactionType.TransTypeID");
                    sbQuery.AppendFormat("      {0} ", parameters);
                    sbQuery.AppendFormat("    UNION ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat(@"transactions.recPayment.Paymentdate, 
                                                 transactions.recPayment.PaymentNo ,
                                                 'Pembayaran' ,                                                 
                                                 'Pembayaran untuk Nomer Invoice ' + transactions.recPayment.InvoiceNo,
                                                 NULL,
                                                 transactions.recPayment.PaymentValue");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"transactions.recPayment
                                                ");
                    sbQuery.AppendFormat("      {0} ", paramPay);
                    sbQuery.AppendFormat(" ) x");
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recInvoice t = new recInvoice(true);
                                t.recBilling.BillingDate = dr[1] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[1].ToString());
                                t.recBilling.BillingNo = dr[2] == DBNull.Value ? string.Empty : dr[2].ToString();
                                t.recTransactionType.TransTypeName = dr[3] == DBNull.Value ? string.Empty : dr[3].ToString();
                                t.recBilling.ForPayment = dr[4] == DBNull.Value ? string.Empty : dr[4].ToString();
                                t.recBilling.PaymentValue = dr[5] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[5].ToString());
                                t.PaymentValue = dr[6] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[6].ToString());
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }
        public List<recInvoice> GetListHistoryReport(int pageSize, int currentPage, string sortName, string sortOrder, string parameters, string parametersInvoiceNo)
        {
            List<recInvoice> tt = new List<recInvoice>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);
                    string paramPay = string.Empty;
                    paramPay = parameters.Replace("Billing", "Payment");
                    string paramPay2 = string.Empty;
                    paramPay2 = parametersInvoiceNo.Replace("Billing", "Payment");
                    //paramPay = parameters.Replace("recBilling.debtorsectorid", "recPayment.DebtorSectorid");
                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat(@"     Date,NoTrans,JenTrans,Keterangan,Debet,Kredit
                                                from
                                                (
                                                select
                                                transactions.recBilling.Billingdate as 'Date', 
                                                 transactions.recBilling.BillingNo as 'NoTrans',
                                                 master.recTransactionType.TransTypeName as 'JenTrans',                                                 
                                                 transactions.recBilling.ForPayment as 'Keterangan',
                                                 case master.recTransactionType.TransTypeName
                                                 when 'Credit Note' then NULL
                                                 else transactions.recBilling.value end as 'Debet',
                                                 case master.recTransactionType.TransTypeName
                                                 when 'Credit Note' then transactions.recBilling.value 
                                                 else NULL end as 'Kredit'");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"transactions.recBilling
                                            INNER JOIN master.recTransactionType ON transactions.recBilling.TransTypeID = master.recTransactionType.TransTypeID");
                    sbQuery.AppendFormat("      {0} ", parameters);
                    sbQuery.AppendFormat("    UNION ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat(@"transactions.recPayment.Paymentdate, 
                                                 transactions.recPayment.PaymentNo ,
                                                 'Pembayaran' ,                                                 
                                                 'Pembayaran untuk Nomer Invoice ' + transactions.recPayment.InvoiceNo,
                                                 NULL,
                                                 transactions.recPayment.PaymentValue");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"transactions.recPayment
                                                ");
                    sbQuery.AppendFormat("      {0} ", paramPay);
                    sbQuery.AppendFormat("    UNION ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat(@"transactions.recBilling.Billingdate as 'Date', 
                                                 transactions.recBilling.BillingNo as 'NoTrans',
                                                 master.recTransactionType.TransTypeName as 'JenTrans',                                                 
                                                 transactions.recBilling.ForPayment as 'Keterangan',
                                                 case master.recTransactionType.TransTypeName
                                                 when 'Credit Note' then NULL
                                                 else transactions.recBilling.value end as 'Debet',
                                                 case master.recTransactionType.TransTypeName
                                                 when 'Credit Note' then transactions.recBilling.value 
                                                 else NULL end as 'Kredit'");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"transactions.recBilling
                                            INNER JOIN master.recTransactionType ON transactions.recBilling.TransTypeID = master.recTransactionType.TransTypeID
                                                ");
                    sbQuery.AppendFormat("      {0} ", parametersInvoiceNo);
                    sbQuery.AppendFormat("    UNION ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat(@"transactions.recPayment.Paymentdate, 
                                                 transactions.recPayment.PaymentNo ,
                                                 'Pembayaran' ,                                                 
                                                 'Pembayaran untuk Nomer Invoice ' + transactions.recPayment.InvoiceNo,
                                                 NULL,
                                                 transactions.recPayment.PaymentValue");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"transactions.recPayment
                                                ");
                    sbQuery.AppendFormat("      {0} ", paramPay2);
                    sbQuery.AppendFormat(" ) x");
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recInvoice t = new recInvoice(true);
                                t.recBilling.BillingDate = dr[1] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[1].ToString());
                                t.recBilling.BillingNo = dr[2] == DBNull.Value ? string.Empty : dr[2].ToString();
                                t.recTransactionType.TransTypeName = dr[3] == DBNull.Value ? string.Empty : dr[3].ToString();
                                t.recBilling.ForPayment = dr[4] == DBNull.Value ? string.Empty : dr[4].ToString();
                                t.recBilling.PaymentValue = dr[5] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[5].ToString());
                                t.PaymentValue = dr[6] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[6].ToString());
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }
        public DataTable GetListHistoryReport(string parameterbill, string parameterpayment)
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    StringBuilder sbQueryPayment = new StringBuilder();
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat(@"     transactions.recBilling.Billingdate as 'TglTrans', 
                                                 transactions.recBilling.BillingNo as 'NoTrans',
                                                 master.recTransactionType.TransTypeName as 'JenTrans',                                                 
                                                 transactions.recBilling.ForPayment as 'Keterangan',
                                                 case master.recTransactionType.TransTypeName
                                                 when 'Credit Note' then NULL
                                                 else transactions.recBilling.value end as 'Debet',
                                                 case master.recTransactionType.TransTypeName
                                                 when 'Credit Note' then transactions.recBilling.value 
                                                 else NULL end as 'Kredit'");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"transactions.recBilling
                                            INNER JOIN master.recTransactionType ON transactions.recBilling.TransTypeID = master.recTransactionType.TransTypeID");
                    sbQuery.AppendFormat("      {0} ", parameterbill);
                    sbQuery.AppendFormat("    UNION ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat(@"transactions.recPayment.Paymentdate, 
                                                 transactions.recPayment.PaymentNo ,
                                                 'Pembayaran',                                                 
                                                 'Pembayaran untuk Nomer Invoice ' + transactions.recPayment.InvoiceNo as 'Keterangan',
                                                 NULL as 'Debet',
                                                 transactions.recPayment.PaymentValue as 'Kredit'");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"transactions.recPayment
                                                ");
                    sbQuery.AppendFormat("      {0} ", parameterpayment);

                    cmd.CommandText = sbQuery.ToString();
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;


                    try
                    {
                        conn.Open();
                        da.Fill(dt);

                    }
                    catch (Exception ex)
                    {

                        throw ex;
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return dt;
        }
        public DataTable GetListHistoryReport(string parameterbill, string parameterpayment, string parameterInvoiceNoBill, string parameterInvoiceNoPayment)
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    StringBuilder sbQueryPayment = new StringBuilder();
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat(@"     transactions.recBilling.Billingdate as 'TglTrans', 
                                                 transactions.recBilling.BillingNo as 'NoTrans',
                                                 master.recTransactionType.TransTypeName as 'JenTrans',                                                 
                                                 transactions.recBilling.ForPayment as 'Keterangan',
                                                 case master.recTransactionType.TransTypeName
                                                 when 'Credit Note' then NULL
                                                 else transactions.recBilling.value end as 'Debet',
                                                 case master.recTransactionType.TransTypeName
                                                 when 'Credit Note' then transactions.recBilling.value 
                                                 else NULL end as 'Kredit'");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"transactions.recBilling
                                            INNER JOIN master.recTransactionType ON transactions.recBilling.TransTypeID = master.recTransactionType.TransTypeID");
                    sbQuery.AppendFormat("      {0} ", parameterbill);
                    sbQuery.AppendFormat("    UNION ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat(@"transactions.recPayment.Paymentdate, 
                                                 transactions.recPayment.PaymentNo ,
                                                 'Pembayaran',                                                 
                                                 'Pembayaran untuk Nomer Invoice ' + transactions.recPayment.InvoiceNo as 'Keterangan',
                                                 NULL as 'Debet',
                                                 transactions.recPayment.PaymentValue as 'Kredit'");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"transactions.recPayment
                                                ");
                    sbQuery.AppendFormat("      {0} ", parameterpayment);
                    sbQuery.AppendFormat("    UNION ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat(@"transactions.recBilling.Billingdate as 'Date', 
                                                 transactions.recBilling.BillingNo as 'NoTrans',
                                                 master.recTransactionType.TransTypeName as 'JenTrans',                                                 
                                                 transactions.recBilling.ForPayment as 'Keterangan',
                                                 case master.recTransactionType.TransTypeName
                                                 when 'Credit Note' then NULL
                                                 else transactions.recBilling.value end as 'Debet',
                                                 case master.recTransactionType.TransTypeName
                                                 when 'Credit Note' then transactions.recBilling.value 
                                                 else NULL end as 'Kredit'");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"transactions.recBilling
                                            INNER JOIN master.recTransactionType ON transactions.recBilling.TransTypeID = master.recTransactionType.TransTypeID
                                                ");
                    sbQuery.AppendFormat("      {0} ", parameterInvoiceNoBill);
                    sbQuery.AppendFormat("    UNION ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat(@"transactions.recPayment.Paymentdate, 
                                                 transactions.recPayment.PaymentNo ,
                                                 'Pembayaran' ,                                                 
                                                 'Pembayaran untuk Nomer Invoice ' + transactions.recPayment.InvoiceNo,
                                                 NULL,
                                                 transactions.recPayment.PaymentValue");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"transactions.recPayment
                                                ");
                    sbQuery.AppendFormat("      {0} ", parameterInvoiceNoPayment);
                    cmd.CommandText = sbQuery.ToString();
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;


                    try
                    {
                        conn.Open();
                        da.Fill(dt);

                    }
                    catch (Exception ex)
                    {

                        throw ex;
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return dt;
        }

        public bool GetYearsReportYearsDues(string billingNo)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"select TOP 1 ISNULL(recImposition.YearReport,0)  YearReport
                                        ,ISNULL(recCalculation.YearDues,0) YearDues
                                        from transactions.recBilling
                                        inner join transactions.recCalculation on recBilling.CalculationID = recCalculation.CalculationID
                                        inner join transactions.recImpositionDetail on recImpositionDetail.ImpositionDetailID = recImpositionDetail.ImpositionDetailID
                                        inner join transactions.recImposition on recImpositionDetail.ImpositionID = recImposition.ImpositionID
                                        where recBilling.BillingNo=@BillingNo";

                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = billingNo;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                if (Convert.ToInt32(dr[0]) < (Convert.ToInt32(dr[1]) - 1))
                                {
                                    result = true;
                                }
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return result;
        }
        public List<recInvoice> GetList(string DebtID)
        {
            List<recInvoice> tt = new List<recInvoice>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"SELECT a.InvoiceNo, a.InvoiceDate, a.InvoiceValue, a.BankID, a.BeSpelled, a.PaymentNo, a.PaymentDate, a.PaymentValue, a.IsHapusBuku, a.IsHapusTagih, a.DebtorSectorID, a.CreatedDate, a.CreatedBy, a.UpdatedDate, a.UpdatedBy, a.IsCancel, a.Balance 
                                        FROM transactions.recInvoice a
                                        inner join master.recdebtorsector b on a.debtorsectorid = b.debtorsectorid
                                        inner join master.recdebtor c on b.debtorid = c.debtorid where c.debtorid ="+DebtID+" and InvoiceNo not in (select distinct InvoiceNo from transactions.recbilling where isrestructur = 1 and invoiceno is not null) " ;
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recInvoice t = new recInvoice();
                                t.InvoiceNo = dr[recInvoice.Fields.InvoiceNo] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.InvoiceNo].ToString();
                                t.InvoiceDate = dr[recInvoice.Fields.InvoiceDate] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[recInvoice.Fields.InvoiceDate].ToString());
                                t.InvoiceValue = dr[recInvoice.Fields.InvoiceValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recInvoice.Fields.InvoiceValue].ToString());
                                t.BankID = dr[recInvoice.Fields.BankID] == DBNull.Value ? (short)0 : short.Parse(dr[recInvoice.Fields.BankID].ToString());
                                t.BeSpelled = dr[recInvoice.Fields.BeSpelled] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.BeSpelled].ToString();
                                t.PaymentNo = dr[recInvoice.Fields.PaymentNo] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.PaymentNo].ToString();
                                t.PaymentDate = dr[recInvoice.Fields.PaymentDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recInvoice.Fields.PaymentDate].ToString());
                                t.PaymentValue = dr[recInvoice.Fields.PaymentValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recInvoice.Fields.PaymentValue].ToString());
                                t.IsHapusBuku = dr[recInvoice.Fields.IsHapusBuku] == DBNull.Value ? (bool?)null : bool.Parse(dr[recInvoice.Fields.IsHapusBuku].ToString());
                                t.IsHapusTagih = dr[recInvoice.Fields.IsHapusTagih] == DBNull.Value ? (bool?)null : bool.Parse(dr[recInvoice.Fields.IsHapusTagih].ToString());
                                t.DebtorSectorID = dr[recInvoice.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recInvoice.Fields.DebtorSectorID].ToString());
                                t.CreatedDate = dr[recInvoice.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recInvoice.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recInvoice.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recInvoice.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recInvoice.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recInvoice.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.UpdatedBy].ToString();
                                t.IsCancel = dr[recInvoice.Fields.IsCancel] == DBNull.Value ? (bool?)null : bool.Parse(dr[recInvoice.Fields.IsCancel].ToString());
                                t.Balance = dr[recInvoice.Fields.Balance] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recInvoice.Fields.Balance].ToString());
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }
        public List<recInvoice> GetListEdit(string DebtID, string resID)
        {
            List<recInvoice> tt = new List<recInvoice>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"SELECT a.InvoiceNo, a.InvoiceDate, a.InvoiceValue, a.BankID, a.BeSpelled, a.PaymentNo, a.PaymentDate, a.PaymentValue, a.IsHapusBuku, a.IsHapusTagih, a.DebtorSectorID, a.CreatedDate, a.CreatedBy, a.UpdatedDate, a.UpdatedBy, a.IsCancel, a.Balance 
                                        FROM transactions.recInvoice a
                                        inner join master.recdebtorsector b on a.debtorsectorid = b.debtorsectorid
                                        inner join master.recdebtor c on b.debtorid = c.debtorid where c.debtorid =" + DebtID + @" 
                                        and InvoiceNo not in(select invoiceno from transactions.recrestructur 
                                        where invoiceno not in( select invoiceno from transactions.recrestructur where restructurid ="+resID+"))";
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recInvoice t = new recInvoice();
                                t.InvoiceNo = dr[recInvoice.Fields.InvoiceNo] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.InvoiceNo].ToString();
                                t.InvoiceDate = dr[recInvoice.Fields.InvoiceDate] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[recInvoice.Fields.InvoiceDate].ToString());
                                t.InvoiceValue = dr[recInvoice.Fields.InvoiceValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recInvoice.Fields.InvoiceValue].ToString());
                                t.BankID = dr[recInvoice.Fields.BankID] == DBNull.Value ? (short)0 : short.Parse(dr[recInvoice.Fields.BankID].ToString());
                                t.BeSpelled = dr[recInvoice.Fields.BeSpelled] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.BeSpelled].ToString();
                                t.PaymentNo = dr[recInvoice.Fields.PaymentNo] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.PaymentNo].ToString();
                                t.PaymentDate = dr[recInvoice.Fields.PaymentDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recInvoice.Fields.PaymentDate].ToString());
                                t.PaymentValue = dr[recInvoice.Fields.PaymentValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recInvoice.Fields.PaymentValue].ToString());
                                t.IsHapusBuku = dr[recInvoice.Fields.IsHapusBuku] == DBNull.Value ? (bool?)null : bool.Parse(dr[recInvoice.Fields.IsHapusBuku].ToString());
                                t.IsHapusTagih = dr[recInvoice.Fields.IsHapusTagih] == DBNull.Value ? (bool?)null : bool.Parse(dr[recInvoice.Fields.IsHapusTagih].ToString());
                                t.DebtorSectorID = dr[recInvoice.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recInvoice.Fields.DebtorSectorID].ToString());
                                t.CreatedDate = dr[recInvoice.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recInvoice.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recInvoice.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recInvoice.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recInvoice.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recInvoice.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recInvoice.Fields.UpdatedBy].ToString();
                                t.IsCancel = dr[recInvoice.Fields.IsCancel] == DBNull.Value ? (bool?)null : bool.Parse(dr[recInvoice.Fields.IsCancel].ToString());
                                t.Balance = dr[recInvoice.Fields.Balance] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recInvoice.Fields.Balance].ToString());
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }		
    }
}
